﻿using System.Data;
using Dapper;

namespace SqlSharp.Databases;

internal class MySqlDatabaseProvider(IDbConnection connection) : DatabaseProvider
{
    public override Database Open()
    {
        var tables = GetTables();
        var columns = GetColumns();
        foreach (var table in tables)
        {
            var list = columns.Where(column => column["TABLE_NAME"] == table["TABLE_NAME"]).ToList();
            table.AddColumns(list);
        }

        return new Database(connection.Database, tables);
    }

    private List<TableInfo> GetTables()
    {
        const string sql = """

                            SELECT     
                                *
                            FROM 
                                information_schema.TABLES 
                            WHERE 
                                TABLE_SCHEMA = @Database
                                               
                           """;
        var result = connection.Query(sql, new { connection.Database });
        var tables = new List<TableInfo>();
        foreach (var item in result)
        {
            var annotations = DbUtility.ToDictionary(item);
            var name = annotations["TABLE_NAME"];
            var description = annotations["TABLE_COMMENT"];
            var table = new TableInfo(name, description, annotations);
            tables.Add(table);
        }

        return tables;
    }

    private List<ColumnInfo> GetColumns()
    {
        const string sql = """

                            SELECT 
                                *
                            FROM 
                                information_schema.COLUMNS 
                            WHERE 
                                TABLE_SCHEMA = @Database
                                               
                           """;
        var result = connection.Query(sql, new { connection.Database });
        var columns = new List<ColumnInfo>();
        foreach (var item in result)
        {
            var annotations = DbUtility.ToDictionary(item);
            var name = annotations["COLUMN_NAME"];
            var type = annotations["DATA_TYPE"];
            var description = annotations["COLUMN_COMMENT"];
            columns.Add(new ColumnInfo(name, type, description, annotations));
        }

        return columns;
    }
}